import random

import check
import material
import mes_routing
import mes_routing_base
import system
from conf import *
from utils import *
from database import Mysql
import logging
import mrp  # 新增导入mrp模块
import prod
import datetime  # 确保导入datetime模块
import store

# 公共时间范围生成函数
# def get_time_ranges():
#     now = datetime.datetime.now()
#     one_year_ago = now - datetime.timedelta(days=begin_time_distance_curr_days)
#     one_week_ago = now - datetime.timedelta(days=end_time_distance_curr_days)
#     return one_year_ago, one_week_ago

# 根据g_start_date和g_end_date生成随机时间
def generate_random_time():
    start_date = g_start_date
    end_date = g_end_date
    start_datetime = datetime.datetime.strptime(start_date, "%Y-%m-%d")
    end_datetime = datetime.datetime.strptime(end_date, "%Y-%m-%d")

    time_diff = end_datetime - start_datetime
    total_seconds = int(time_diff.total_seconds())
    random_seconds = random.randint(0, total_seconds)
    random_time = start_datetime + datetime.timedelta(seconds=random_seconds)
    return random_time

# 根据g_start_date、g_end_date、g_split_date和g_early_weight生成随机时间
def generate_weighted_random_time():
    """
    生成偏向 split_date 之前的时间点
    :return: 随机生成的datetime对象
    """
    start_date = g_start_date
    end_date = g_end_date
    split_date = g_split_date
    early_weight = g_early_weight

    # 转换为datetime对象
    if isinstance(start_date, str):
        start_date = datetime.datetime.strptime(start_date, "%Y-%m-%d")
    if isinstance(end_date, str):
        end_date = datetime.datetime.strptime(end_date, "%Y-%m-%d")
    if isinstance(split_date, str):
        split_date = datetime.datetime.strptime(split_date, "%Y-%m-%d")

    # 计算总时间范围和分割点
    total_seconds = int((end_date - start_date).total_seconds())
    split_seconds = int((split_date - start_date).total_seconds())

    # 按权重选择时间段
    if random.random() < early_weight:  # 80%概率生成 split_date 之前的时间
        random_seconds = random.randint(0, split_seconds)
    else:  # 20%概率生成 split_date 之后的时间
        random_seconds = random.randint(split_seconds, total_seconds)

    return start_date + timedelta(seconds=random_seconds)

######################################################################
# 销售相关数据更新
######################################################################

# 1、更新销售订单表sale_order，
# 设置order_time、create_time、audit_time
def update_sale_order(db):
    select_sql = "SELECT id FROM sale_order ORDER BY id DESC"
    update_sql = "UPDATE sale_order SET order_time = %s, create_time = %s, audit_time = %s WHERE id = %s"
    results = db.get_all(select_sql)
    # 当前时间往前推74个小时，毫秒级
    t = int((datetime.datetime.now() - datetime.timedelta(hours=74)).timestamp() * 1000)
    step = 50  # 间隔多久
    random_step = 4  # 间隔多久的随机小时
    for result in results:
        id = int(result["id"])
        order_time = adjust_time(t)  # 先对t做校验保证不在晚上
        create_time = t + (random.randint(30, 60)) * 1000
        audit_time = t + (random.randint(120, 600)) * 1000
        create_time, audit_time = adjust_times(create_time, audit_time)
        delay = (random.randint(0, random_step * 3600) + step * 3600) * 1000
        t -= delay
        count = db.update(update_sql, (order_time, create_time, audit_time, id))
        if count == 0:
            print(
                "UPDATE 没有影响任何行 {} {}",
                update_sql,
                (order_time, create_time, audit_time, id),
            )
        elif count < 0:
            print(
                "UPDATE 执行失败 {} {}",
                update_sql,
                (order_time, create_time, audit_time, id),
            )
            return
    db.commit()

# 1、更新销售订单表sale_order，
# 设置order_time、create_time、audit_time
# 可以自己控制时间，而不是根据数据的数量来控制
def update_sale_order2(db):
    select_sql = "SELECT id FROM sale_order ORDER BY id DESC"
    update_sql = "UPDATE sale_order SET order_time = %s, create_time = %s, audit_time = %s WHERE id = %s"
    results = db.get_all(select_sql)

    # 判空处理，如果结果为空直接返回
    if not results:
        return
    
    # 按id由大到小排序（确保排序正确）
    results.sort(key=lambda x: int(x["id"]), reverse=True)
    
    t_values = []
    for _ in results:
        if g_is_early_weight:
            random_time = generate_weighted_random_time()
        else:
            random_time = generate_random_time()
        t = adjust_time(int(random_time.timestamp() * 1000))
        t_values.append(t)
    
    # 对t值进行降序排序，确保大id匹配大t
    t_values.sort(reverse=True)
    
    for i, result in enumerate(results):
        t = t_values[i]
        id = int(result["id"])
        order_time = adjust_time(t)  # 先对t做校验保证不在晚上
        create_time = t + (random.randint(30, 60)) * 1000
        audit_time = t + (random.randint(120, 600)) * 1000
        create_time, audit_time = adjust_times(create_time, audit_time)

        count = db.update(update_sql, (order_time, create_time, audit_time, id))
        if count == 0:
            print(
                "UPDATE 没有影响任何行 {} {}",
                update_sql,
                (order_time, create_time, audit_time, id),
            )
        elif count < 0:
            print(
                "UPDATE 执行失败 {} {}",
                update_sql,
                (order_time, create_time, audit_time, id),
            )
            return
    db.commit()

# 2、更新销售订单物料表sale_order_item，
# 设置plan_send_time、out_time、create_time、last_update_time（基于sale_order）
def update_sale_order_item(db):
    select_sql = "SELECT soi.id, so.create_time, so.order_time, so.id as order_id FROM sale_order_item soi JOIN sale_order so ON soi.order_id = so.id"
    results = db.get_all(select_sql)

    update_sql = "UPDATE sale_order_item SET plan_send_time = %s, out_time = %s, create_time = %s, last_update_time = %s WHERE id = %s"
    
    if not results:
        return
    for result in results:
        create_time = result["create_time"]
        order_time = result["order_time"]
        # print(result)
        item_id = int(result["id"])

        # 计算plan_send_time和out_time
        # 物料计划发送时间为订单的创建时间加一个礼拜
        plan_send_time = adjust_time(create_time + 7 * 24 * 60 * 60 * 1000)

        # 物料出库时间为订单的订单时间加一个随机时间60小时到84小时
        out_time = adjust_time(order_time + (random.randint(60, 84)) * 60 * 60 * 1000)

        # 创建时间为订单创建时间加一个随机时间10-30秒
        item_create_time = adjust_time(
            create_time + (random.randint(10, 30) * 1000)
        )

        count = db.update(
            update_sql, (plan_send_time, out_time, item_create_time, item_create_time, item_id)
        )
        if count > 0:
            print("UPDATE 执行成功，影响了 {} 行".format(count))
        else:
            print(
                "UPDATE 执行失败{} {}",
                update_sql,
                (plan_send_time, out_time, item_create_time,item_create_time, item_id),
            )
            return
    db.commit()

# 3、更新销售客户订货单表sale_customer_apply，
# 设置order_time、create_time、audit_time
def update_sale_customer_apply(db):
    select_sql = "SELECT id FROM sale_customer_apply ORDER BY id DESC"
    update_sql = "UPDATE sale_customer_apply SET order_time = %s, create_time = %s, audit_time = %s WHERE id = %s"
    results = db.get_all(select_sql)
    
    # 判空处理，如果结果为空直接返回
    if not results:
        return
    
    # 按id由大到小排序
    results.sort(key=lambda x: int(x["id"]), reverse=True)
    
    t_values = []
    for _ in results:
        if g_is_early_weight:
            random_time = generate_weighted_random_time()
        else:
            random_time = generate_random_time()
        t = adjust_time(int(random_time.timestamp() * 1000))
        t_values.append(t)
    
    # 对t值进行降序排序，确保大id匹配大t
    t_values.sort(reverse=True)
    
    for i, result in enumerate(results):
        t = t_values[i]
        id = int(result["id"])
        order_time = adjust_time(t)  # 先对t做校验保证不在晚上
        create_time = t + (random.randint(30, 60)) * 1000
        audit_time = t + (random.randint(120, 600)) * 1000
        create_time, audit_time = adjust_times(create_time, audit_time)

        count = db.update(update_sql, (order_time, create_time, audit_time, id))
        if count > 0:
            print("UPDATE 执行成功，影响了 {} 行".format(count))
        else:
            print(
                "UPDATE 执行失败{} {}",
                update_sql,
                (order_time, create_time, audit_time, id),
            )
            return
    db.commit()

# 4、更新销售客户订货单物料表sale_customer_apply_item，
# 设置delivery_time、create_time、last_update_time(根据sale_customer_apply表中的create_time和audit_time来)
def update_sale_customer_apply_item(db):

    # 交货时间和创建时间是根据销售订单来的，不会超出9点到18点之间
    delivery_time_step = 3 * 24 * 60 * 60 * 1000
    create_time_step = (random.randint(10, 30)) * 1000

    update_sql = '''
    UPDATE sale_customer_apply_item scai
    JOIN sale_customer_apply sca ON scai.apply_id = sca.id
    SET scai.delivery_time   = sca.create_time + %s,
    scai.create_time     = sca.audit_time + %s,
    scai.last_update_time=sca.audit_time + %s;
    '''
    
    # 更新 sale_customer_apply_item 表
    count = db.update(
        update_sql, (delivery_time_step, create_time_step, create_time_step)
    )
    if count > 0:
        print("UPDATE 执行成功，影响了 {} 行".format(count))
    else:
        print(
            "UPDATE 执行失败{} {}",
            update_sql,
            (delivery_time_step, create_time_step, create_time_step),
        )
    db.commit()

# 5、更新销售申请单sale_order_apply，
# 设置order_time、create_time、audit_time
def update_sale_order_apply(db):
    select_sql = "SELECT id FROM sale_order_apply ORDER BY id DESC"
    update_sql = "UPDATE sale_order_apply SET order_time = %s, create_time = %s, audit_time = %s WHERE id = %s"
    results = db.get_all(select_sql)
    
    # 判空处理，如果结果为空直接返回
    if not results:
        return
    
    # 按id由大到小排序
    results.sort(key=lambda x: int(x["id"]), reverse=True)
    
    t_values = []
    for _ in results:
        if g_is_early_weight:
            random_time = generate_weighted_random_time()
        else:
            random_time = generate_random_time()
        t = adjust_time(int(random_time.timestamp() * 1000))
        t_values.append(t)
    
    # 对t值进行降序排序，确保大id匹配大t
    t_values.sort(reverse=True)
    
    for i, result in enumerate(results):
        t = t_values[i]
        id = int(result["id"])
        order_time = adjust_time(t)  # 先对t做校验保证不在晚上
        create_time = t + (random.randint(30, 60)) * 1000
        audit_time = t + (random.randint(120, 600)) * 1000
        create_time, audit_time = adjust_times(create_time, audit_time)

        count = db.update(update_sql, (order_time, create_time, audit_time, id))
        if count > 0:
            print("UPDATE 执行成功，影响了 {} 行".format(count))
        else:
            print(
                "UPDATE 执行失败{} {}",
                update_sql,
                (order_time, create_time, audit_time, id),
            )
            return
    db.commit()

# 6、更新销售申请单物料表sale_order_apply_item，
# 设置delivery_time、create_time、last_update_time(根据sale_order_apply表中的create_time和audit_time来)
def update_sale_order_apply_item(db):
    # 交货时间和创建时间是根据销售订单来的，不会超出9点到18点之间
    delivery_time_step = 3 * 24 * 60 * 60 * 1000
    create_time_step = (random.randint(10, 30)) * 1000

    update_sql = '''
    UPDATE sale_order_apply_item scai
    JOIN sale_order_apply sca ON scai.order_id = sca.id
    SET scai.delivery_time   = sca.create_time + %s,
    scai.create_time     = sca.audit_time + %s,
    scai.last_update_time=sca.audit_time + %s;
    '''
    
    # 更新 sale_customer_apply_item 表
    count = db.update(
        update_sql, (delivery_time_step, create_time_step, create_time_step)
    )
    if count > 0:
        print("UPDATE 执行成功，影响了 {} 行".format(count))
    else:
        print(
            "UPDATE 执行失败{} {}",
            update_sql,
            (delivery_time_step, create_time_step, create_time_step)
        )
    db.commit()

# 7、更新wms_outbound_form表（与销售出库相关），
# 设置create_time、bill_time和last_update_time（基于sale_order和sale_order_item）
# todo 数据远不止相关联的这些。
def update_wms_outbound_form_sale(db):
    # 1. 先查询需要更新的数据
    select_sql = """
        SELECT 
            wof.id,
            so.order_code,
            so.audit_time,
            soi.out_time
        FROM wms_outbound_form wof
        JOIN sale_order so ON wof.relate_order_code = so.order_code
        JOIN sale_order_item soi ON soi.order_id = so.id
        WHERE so.audit_time IS NOT NULL and wof.bill_type = '销售出库'
    """
    results = db.get_all(select_sql)

    # 2. 准备更新SQL
    update_sql = """
        UPDATE wms_outbound_form 
        SET last_update_time = %s, 
            create_time = %s, 
            bill_time = %s 
        WHERE id = %s
    """

    if not results:
        print("没有找到需要更新的数据")
        return

    # 3. 遍历结果并更新
    for result in results:
        id = int(result["id"])
        order_code = result["order_code"]
        audit_time = result["audit_time"]
        out_time = result["out_time"]

        # 生成10-30秒的随机毫秒数
        create_time_step = random.randint(10, 30) * 1000
        new_time = audit_time + create_time_step

        # 调整到工作时间
        new_time = adjust_time(new_time)
        out_time = adjust_time(out_time)

        # 执行更新
        count = db.update(update_sql, (out_time, new_time, new_time, id))
        if count > 0:
            print(f"UPDATE 销售出库单信息成功，ID: {id}, 订单号: {order_code}")
        else:
            print(f"UPDATE 执行失败 ID: {id}, 订单号: {order_code}")
            return

    # 4. 提交事务
    db.commit()
    print(f"UPDATE 执行完成，共更新了 {len(results)} 条记录")

# 8、更新发货记录表sale_order_send，
# 设置create_time、last_update_time（为sale_order_item表最新时间的out_time）
def update_sale_order_send(db):
    update_sql = """
        UPDATE sale_order_send sos
            JOIN (SELECT soi.order_id, soi.out_time
                  FROM sale_order_item soi
                  INNER JOIN (
                              SELECT order_id,MIN(id) AS min_id
                              FROM sale_order_item
                              GROUP BY order_id
                              ) cmi
                  ON soi.id = cmi.min_id) cot
            ON sos.order_id = cot.order_id
        SET sos.create_time      = cot.out_time,
            sos.last_update_time = cot.out_time;
    """

    count = db.update(update_sql)
    if count > 0:
        print("UPDATE 执行成功，影响了 {} 行".format(count))
    else:
        print("UPDATE 执行失败{} {}", update_sql)
        return
    db.commit()

# 9、更新act_bpmn_process_log（与sale_order相关联的数据），
# 设置租户的销售订单“发起审核”的创建者id、创建者姓名
# 及对应的“发起审核、审核”操作类型的创建时间和最后更新时间
def update_act_bpmn_process_log(db):
    # 更新 creator_id 和 creator_name
    update_sql1 = """
        UPDATE ecloud_bpmn.act_bpmn_process_log 
        SET creator_id = %s, creator_name = %s 
        WHERE tenant_id = %s AND business_type = %s AND operate_name = %s
    """
    count = db.update(update_sql1, (sales_dept_staff['id'], sales_dept_staff['name'], TenantId, '销售订单', '发起审核'))
    if count > 0:
        print("UPDATE 执行成功，影响了 {} 行".format(count))
    else:
        print("UPDATE 执行失败{} {}".format(update_sql1, (
        sales_dept_staff['id'], sales_dept_staff['name'], TenantId, '销售订单', '发起审核')))

    # 更新 create_time 和 last_update_time
    select_sql = """
        SELECT log.id, log.operate_name, so.create_time, so.audit_time 
        FROM ecloud_bpmn.act_bpmn_process_log log
        JOIN sale_order so ON log.process_instance_id = so.process_id
        WHERE log.tenant_id = %s AND log.operate_name IN (%s, %s)
    """
    results = db.get_all(select_sql, (TenantId, '发起审核', '审核'))
    update_sql2 = """
        UPDATE ecloud_bpmn.act_bpmn_process_log 
        SET create_time = %s, last_update_time = %s 
        WHERE id = %s
    """
    if not results:
        db.commit()
        return

    for result in results:
        id = int(result["id"])
        operate_name = result["operate_name"]
        create_time = result["create_time"]
        audit_time = result["audit_time"]

        if operate_name == '发起审核':
            new_time = create_time
        elif operate_name == '审核':
            new_time = audit_time
        else:
            continue

        count = db.update(update_sql2, (new_time, new_time, id))
        if count > 0:
            print("UPDATE 执行成功，影响了 {} 行".format(count))
        else:
            print("UPDATE 执行失败{} {}".format(update_sql2, (new_time, new_time, id)))
            return
    db.commit()

#########################################################
# 生产计划相关数据更新
#########################################################

# 10、更新mrp_project表，设置creator_id和creator_name；（销售部门人员id和姓名）
# 更新mrp_produce_plan表的submit_id和submit_name；（销售部门人员id和姓名）
# 更新mrp_purchase_plan的submit_id和submit_name（销售部门人员id和姓名）
def update_mrp_tables(db):
    # 更新 mrp_project 表
    update_sql1 = """
        UPDATE mrp_project 
        SET creator_id = %s, creator_name = %s
    """
    count = db.update(update_sql1, (sales_dept_staff["id"], sales_dept_staff["name"]))
    if count > 0:
        print("UPDATE mrp_project 执行成功，影响了 {} 行".format(count))
    else:
        print(
            "UPDATE mrp_project 执行失败{} {}".format(update_sql1, (sales_dept_staff["id"], sales_dept_staff["name"])))

    # 更新 mrp_produce_plan 表
    update_sql2 = """
        UPDATE mrp_produce_plan 
        SET submit_id = %s, submit_name = %s
    """
    count = db.update(update_sql2, (sales_dept_staff["id"], sales_dept_staff["name"]))
    if count > 0:
        print("UPDATE mrp_produce_plan 执行成功，影响了 {} 行".format(count))
    else:
        print("UPDATE mrp_produce_plan 执行失败{} {}".format(update_sql2,
                                                             (sales_dept_staff["id"], sales_dept_staff["name"])))

    # 更新 mrp_purchase_plan 表
    update_sql3 = """
        UPDATE mrp_purchase_plan 
        SET submit_id = %s, submit_name = %s
    """
    count = db.update(update_sql3, (sales_dept_staff["id"], sales_dept_staff["name"]))
    if count > 0:
        print("UPDATE mrp_purchase_plan 执行成功，影响了 {} 行".format(count))
    else:
        print("UPDATE mrp_purchase_plan 执行失败{} {}".format(update_sql3,
                                                              (sales_dept_staff["id"], sales_dept_staff["name"])))
    db.commit()

# 12、更新wms_outbound_form表（与生产领料相关），
# 设置create_time、last_update_time和bill_time（基于mes_task_master的actual_start_time）
def update_wms_outbound_form_produce(db):
    select_sql = """
        SELECT wf.id, mt.bill_time, mt.actual_start_time 
        FROM wms_outbound_form wf
        JOIN mes_task_master mt ON wf.task_no = mt.task_no
        WHERE mt.bill_time IS NOT NULL and wf.bill_type = '生产领料'
    """
    results = db.get_all(select_sql)
    update_sql = """
        UPDATE wms_outbound_form 
        SET bill_time = %s, create_time = %s, last_update_time = %s 
        WHERE id = %s
    """
    if not results:
        return
    for result in results:
        id = int(result["id"])
        bill_time = result["bill_time"]
        actual_start_time = result["actual_start_time"]

        # actual_start_time 加 3 到 5 分钟的随机毫秒数
        new_time = actual_start_time + random.randint(3 * 60 * 1000, 5 * 60 * 1000)
        new_time = adjust_time(new_time)

        count = db.update(update_sql, (bill_time, new_time, new_time, id))
        if count > 0:
            print("UPDATE 执行成功，影响了 {} 行".format(count))
        else:
            print("UPDATE 执行失败{} {}".format(update_sql, (bill_time, new_time, new_time, id)))
            return
    db.commit()

# 13、更新wms_inbound_form表（与产成品入库相关），
# 设置create_time、last_update_time和bill_time（基于mes_task_master的actual_end_time）
def update_wms_inbound_form(db):
    select_sql = """
        SELECT wf.id, mt.bill_time, mt.actual_end_time 
        FROM wms_inbound_form wf
        JOIN mes_task_master mt ON wf.task_no = mt.task_no
        WHERE mt.bill_time IS NOT NULL and wf.bill_type = '产成品入库'
    """
    results = db.get_all(select_sql)
    update_sql = """
        UPDATE wms_inbound_form 
        SET create_time = %s, last_update_time = %s, bill_time = %s 
        WHERE id = %s
    """
    if not results:
        return
    for result in results:
        id = int(result["id"])
        bill_time = result["bill_time"]
        actual_end_time = result["actual_end_time"]

        # actual_end_time 加 3 到 5 分钟的随机毫秒数
        new_time = actual_end_time + random.randint(3 * 60 * 1000, 5 * 60 * 1000)
        new_time = adjust_time(new_time)

        count = db.update(update_sql, (new_time, new_time, bill_time, id))
        if count > 0:
            print("UPDATE 执行成功，影响了 {} 行".format(count))
        else:
            print("UPDATE 执行失败{} {}".format(update_sql, (new_time, new_time, bill_time, id)))
            return
    db.commit()

##############################################################
# 采购相关信息更新
##############################################################

# 14、更新purchase_order_info表，
# 设置order_time、create_time、audit_time、plan_start_time、plan_end_time
def update_purchase_order_info(db):
    select_sql = "SELECT id FROM purchase_order_info ORDER BY id DESC"
    update_sql = "UPDATE purchase_order_info SET order_time = %s, create_time = %s, audit_time = %s, plan_start_time = %s, plan_end_time = %s WHERE id = %s"
    results = db.get_all(select_sql)

    # 判空处理，如果结果为空直接返回
    if not results:
        return
    
    # 按id由大到小排序
    results.sort(key=lambda x: int(x["id"]), reverse=True)
    
    t_values = []
    for _ in results:
        if g_is_early_weight:
            random_time = generate_weighted_random_time()
        else:
            random_time = generate_random_time()
        t = adjust_time(int(random_time.timestamp() * 1000))
        t_values.append(t)
    
    # 对t值进行降序排序，确保大id匹配大t
    t_values.sort(reverse=True)

    for i, result in enumerate(results):
        t = t_values[i]

        id = int(result["id"])
        order_time = adjust_time(t)  # 先对t做校验保证不在晚上
        create_time = t + (random.randint(30, 60)) * 1000
        audit_time = t + (random.randint(120, 600)) * 1000
        create_time, audit_time = adjust_times(create_time, audit_time)

        # 计划开始和结束时间
        plan_start_time = t + (random.randint(1, 2))* 24 * 60 * 60 * 1000
        plan_end_time = t + (random.randint(3, 6))* 24 * 60 * 60 * 1000


        count = db.update(update_sql, (order_time, create_time, audit_time, plan_start_time, plan_end_time, id))
        if count == 0:
            print(
                "UPDATE 没有影响任何行 {} {}",
                update_sql,
                (order_time, create_time, audit_time, plan_start_time, plan_end_time, id),

            )
        elif count < 0:
            print(
                "UPDATE 执行失败 {} {}",
                update_sql,
                (order_time, create_time, audit_time, plan_start_time, plan_end_time, id),
            )
            return
    db.commit()

# 15、更新purchase_order_goods_info表，
# 设置plan_receive_time、in_time、create_time、last_update_time（根据purchase_order_info的create_time来）
def update_purchase_order_goods_info(db):
    select_sql = "select pogi.id, poi.create_time from purchase_order_goods_info pogi JOIN purchase_order_info poi ON pogi.purchase_order_id = poi.id"
    update_sql = "UPDATE purchase_order_goods_info SET plan_receive_time = %s, in_time = %s, create_time = %s, last_update_time = %s WHERE id = %s"
    results = db.get_all(select_sql)
    if not results:
        return
    for result in results:
        createTime = result["create_time"]
        planReceivceTime = createTime + 7 * 24 * 60 * 60 * 1000
        random_hours = adjust_time(createTime + random.randint(70, 76)*60 *60 *1000)
        id = int(result["id"])

        count = db.update(update_sql, (planReceivceTime, random_hours, createTime, createTime, id))
        if count > 0:
            print(f"UPDATE 采购物料信息成功，影响了 {count} 行")
        else:
            print("UPDATE 采购物料信息执行失败")
            return;
    db.commit()

# 16、更新wms_inbound_form表（与采购入库相关），
# 设置create_time、last_update_time、bill_time（根据purchase_order_info的audit_time来）
def update_wms_inbound_form_purchase(db):
    # 1. 先查询需要更新的数据
    select_sql = """
        SELECT 
            wf.id, 
            poi.order_time AS bill_time, 
            poi.audit_time,
            wf.relate_order_code
        FROM wms_inbound_form wf
        JOIN purchase_order_info poi ON wf.relate_order_code = poi.code
        WHERE poi.audit_time IS NOT NULL and wf.bill_type = '采购入库'
    """
    results = db.get_all(select_sql)

    # 2. 准备更新SQL
    update_sql = """
        UPDATE wms_inbound_form 
        SET create_time = %s, last_update_time = %s, bill_time = %s 
        WHERE id = %s
    """
    if not results:
        return
    # 3. 遍历结果并更新
    for result in results:
        id = int(result["id"])
        bill_time = result["bill_time"]
        audit_time = result["audit_time"]

        # 生成60-92小时的随机毫秒数
        random_hours_ms = random.randint(60, 92) * 60 * 60 * 1000
        new_time = audit_time + random_hours_ms

        # 调整到工作时间
        new_time = adjust_time(new_time)

        # 执行更新
        count = db.update(update_sql, (new_time, new_time, bill_time, id))
        if count > 0:
            print(f"UPDATE 采购入库单信息成功，ID: {id}, 订单号: {result['relate_order_code']}")
        else:
            print(f"UPDATE 执行失败 ID: {id}, 订单号: {result['relate_order_code']}")
            return

    # 4. 提交事务
    db.commit()

# 17、更新purchase_order_receive_goods_info表，
# 设置receive_time、last_update_time、create_time（基于purchase_order_info的audit_time）
def update_purchase_order_receive_goods_info(db):
    # 1. 先查询需要更新的数据
    select_sql = """
        SELECT 
            porgi.id,
            poi.code AS relate_order_code,
            poi.order_time AS bill_time,
            poi.audit_time
        FROM purchase_order_receive_goods_info porgi
        JOIN purchase_order_info poi ON porgi.purchase_order_id  = poi.id
        WHERE poi.audit_time IS NOT NULL
    """
    results = db.get_all(select_sql)

    # 2. 准备更新SQL
    update_sql = """
        UPDATE purchase_order_receive_goods_info 
        SET receive_time = %s, 
            last_update_time = %s, 
            create_time = %s
        WHERE id = %s
    """
    if not results:
        return
    # 3. 遍历结果并更新
    for result in results:
        id = int(result["id"])
        audit_time = result["audit_time"]

        # 生成60-63小时的随机毫秒数
        random_hours_ms = random.randint(60, 63) * 60 * 60 * 1000
        new_time = audit_time + random_hours_ms

        # 调整到工作时间
        new_time = adjust_time(new_time)

        # 执行更新
        count = db.update(update_sql, (new_time, new_time, new_time, id))
        if count > 0:
            print(f"UPDATE 采购收货时间成功，ID: {id}, 订单号: {result['relate_order_code']}")
        else:
            print(f"UPDATE 执行失败 ID: {id}, 订单号: {result['relate_order_code']}")
            return

    # 4. 提交事务
    db.commit()

# 18、更新act_bpmn_process_log表，
# 设置指定tenant_id的‘采购订单’的操作类型为‘发起审核’的数据为采购部人员的id与姓名；
# 更新purchase_order_advance_payment_info表，
# 设置预付款人的id和姓名为金融部人员的id与姓名
def update_purchase_operators(db):
    # 更新审核记录操作人
    update_sql1 = """
        UPDATE ecloud_bpmn.act_bpmn_process_log
        SET
            creator_id = %s,
            creator_name = %s
        WHERE
            tenant_id = %s
          AND
            business_type = '采购订单'
          AND
            operate_name = '发起审核'
    """
    count1 = db.update(update_sql1,(purchase_dept_staff["id"], purchase_dept_staff["name"], TenantId))
    # 更新预付款信息操作人
    update_sql2 = """
        UPDATE purchase_order_advance_payment_info
        SET creator_id = %s, creator_name = %s
    """
    count2 = db.update(update_sql2,(finance_dept_staff["id"], finance_dept_staff["name"]))
    print(f"UPDATE 采购操作人成功，审核记录影响 {count1} 行，预付款信息影响 {count2} 行")
    db.commit()

# 19、更新act_bpmn_process_log表（与采购相关，指定租户的‘发起审核’和‘审核’操作），
# 设置create_time和last_update_time（基于purchase_order_info的create_time和audit_time）
def update_purchase_operation_time(db):

    try:
        # 1. 先查询需要更新的数据
        select_sql = """
            SELECT 
                log.id,
                log.operate_name,
                poi.create_time AS order_create_time,
                poi.audit_time AS order_audit_time,
                log.process_instance_id
            FROM ecloud_bpmn.act_bpmn_process_log log
            JOIN purchase_order_info poi ON log.process_instance_id = poi.process_id
            WHERE log.tenant_id = %s 
              AND log.operate_name IN (%s, %s)
        """
        results = db.get_all(select_sql, (TenantId, '发起审核', '审核'))

        # 2. 准备更新SQL
        update_sql = """
            UPDATE ecloud_bpmn.act_bpmn_process_log
            SET create_time = %s,
                last_update_time = %s
            WHERE id = %s
        """

        updated_count = 0

        if not results:
            print("没有需要更新的记录")
            db.commit()
            return
        # 3. 遍历结果并更新
        for record in results:
            # 确定更新时间
            if record["operate_name"] == "发起审核":
                new_time = record["order_create_time"]
            elif record["operate_name"] == "审核":
                new_time = record["order_audit_time"]
            else:
                continue  # 理论上不会走到这里，因为查询已过滤

            # 执行更新
            count = db.update(update_sql, (new_time, new_time, record["id"]))
            if count > 0:
                updated_count += 1
                print(
                    f"UPDATE 操作时间更新成功 - ID: {record['id']}, 操作类型: {record['operate_name']}, 流程实例ID: {record['process_instance_id']}")
            else:
                print(f"UPDATE 操作时间更新失败 - ID: {record['id']}")
                return

        # 4. 提交事务
        db.commit()
        print(f"采购操作时间更新完成，共处理了 {updated_count} 条记录")

    except Exception as e:
        # db.rollback()
        print(f"更新过程中发生错误: {str(e)}")
        raise

# 同步销售出库及生产领料的时间至拣货管理
def syn_pick_management(db):
    sql1 = """
        UPDATE wms_outbound_pick_form wop
        JOIN wms_outbound_form wo ON wop.bill_code = wo.bill_code
        SET
            wop.create_time = wo.create_time,
            wop.last_update_time = wo.last_update_time,
            wop.bill_time = wo.bill_time
    """
    count1 = db.update(sql1)
    print(f"SQL1 同步捡货管理时间: 影响 {count1} 行")

    sql2 = """
        UPDATE wms_outbound_pick_form_goods wop
        JOIN wms_outbound_form_goods wo ON (wop.form_id = wo.form_id and wop.goods_code = wo.goods_code)
        SET
            wop.create_time = wo.create_time,
            wop.last_update_time = wo.last_update_time
    """
    count2 = db.update(sql2)
    print(f"SQL2 同步捡货物料时间: 影响 {count2} 行")

    db.commit()
    print("所有拣货管理时间已更新完成")

def confirm_backup():
    """确认数据库已备份"""
    count = 0
    while count < 3:
        user_input = input("请确认已经备份该租户数据库！！！！ 输入3次'y'继续：")
        if user_input.lower() == "y":
            count += 1
            print(f"您已输入 {count} 次'y'")
        else:
            count = 0  # 如果输入不是'y'，重新计数
            print("输入错误，请重新输入'y'以确认")
    print("已确认数据库备份，继续执行程序。")

#####################################################################################################
# 特殊情况
#####################################################################################################
# 单独更新产成品入库的时间
def update_wms_inbound_form_product(db):
    select_sql = "SELECT id FROM wms_inbound_form where bill_type = '产成品入库' ORDER BY id DESC"
    update_sql = "UPDATE wms_inbound_form SET bill_time = %s, create_time = %s, last_update_time = %s WHERE id = %s"
    results = db.get_all(select_sql)

    # 判空处理，如果结果为空直接返回
    if not results:
        return

    # 按id由大到小排序（确保排序正确）
    results.sort(key=lambda x: int(x["id"]), reverse=True)

    t_values = []
    for _ in results:
        if g_is_early_weight:
            random_time = generate_weighted_random_time()
        else:
            random_time = generate_random_time()
        t = adjust_time(int(random_time.timestamp() * 1000))
        t_values.append(t)

    # 对t值进行降序排序，确保大id匹配大t
    t_values.sort(reverse=True)

    for i, result in enumerate(results):
        t = t_values[i]
        id = int(result["id"])
        t = adjust_time(t)  # 先对t做校验保证不在晚上

        count = db.update(update_sql, (t, t, t, id))
        if count == 0:
            print(
                "UPDATE 没有影响任何行 {} {}",
                update_sql,
                (t, t, t, id),
            )
        elif count < 0:
            print(
                "UPDATE 执行失败 {} {}",
                update_sql,
                (t, t, t, id),
            )
            return
    db.commit()

# 单独更新生产领料的时间
def update_wms_outbound_form_goods(db):
    select_sql = "SELECT id FROM wms_outbound_form where bill_type = '生产领料' ORDER BY id DESC"
    update_sql = "UPDATE wms_outbound_form SET bill_time = %s, create_time = %s, last_update_time = %s WHERE id = %s"
    results = db.get_all(select_sql)

    # 判空处理，如果结果为空直接返回
    if not results:
        return

    # 按id由大到小排序（确保排序正确）
    results.sort(key=lambda x: int(x["id"]), reverse=True)

    t_values = []
    for _ in results:
        if g_is_early_weight:
            random_time = generate_weighted_random_time()
        else:
            random_time = generate_random_time()
        t = adjust_time(int(random_time.timestamp() * 1000))
        t_values.append(t)

    # 对t值进行降序排序，确保大id匹配大t
    t_values.sort(reverse=True)

    for i, result in enumerate(results):
        t = t_values[i]
        id = int(result["id"])
        t = adjust_time(t)  # 先对t做校验保证不在晚上

        count = db.update(update_sql, (t, t, t, id))
        if count == 0:
            print(
                "UPDATE 没有影响任何行 {} {}",
                update_sql,
                (t, t, t, id),
            )
        elif count < 0:
            print(
                "UPDATE 执行失败 {} {}",
                update_sql,
                (t, t, t, id),
            )
            return
    db.commit()

# 单独刷新任务维护时间
def update_mes_task_master(db):
    select_sql = "SELECT id FROM mes_task_master ORDER BY id DESC"
    update_sql = "UPDATE mes_task_master SET bill_time = %s, create_time = %s, last_update_time = %s WHERE id = %s"
    results = db.get_all(select_sql)

    # 判空处理，如果结果为空直接返回
    if not results:
        return

    # 按id由大到小排序（确保排序正确）
    results.sort(key=lambda x: int(x["id"]), reverse=True)

    t_values = []
    for _ in results:
        if g_is_early_weight:
            random_time = generate_weighted_random_time()
        else:
            random_time = generate_random_time()
        t = adjust_time(int(random_time.timestamp() * 1000))
        t_values.append(t)

    # 对t值进行降序排序，确保大id匹配大t
    t_values.sort(reverse=True)

    for i, result in enumerate(results):
        t = t_values[i]
        id = int(result["id"])
        t = adjust_time(t)  # 先对t做校验保证不在晚上

        count = db.update(update_sql, (t, t, t, id))
        if count == 0:
            print(
                "UPDATE 没有影响任何行 {} {}",
                update_sql,
                (t, t, t, id),
            )
        elif count < 0:
            print(
                "UPDATE 执行失败 {} {}",
                update_sql,
                (t, t, t, id),
            )
            return
    db.commit()


if __name__ == "__main__":
    import sys

    print("系统路径：")
    print(sys.path)
    confirm_backup()
    logging.basicConfig(
        level=logging.INFO,
        format="%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s",
        datefmt="%a, %d %b %Y %H:%M:%S",
        filename="./test.log",
        filemode="a",
    )
    # 连接数据库
    db_conn = Mysql(DbConfig, "ecloud_chain_" + TenantId)
    try:
        print("开始更新数据库...")
        # 1、更新销售单信息表
        print("1、更新销售单信息表")
        update_sale_order2(db_conn)
        # 2、销售订单物料表
        print("2、更新销售订单物料表")
        update_sale_order_item(db_conn)
        # 3、销售客户订货单
        print("3、更新销售客户订货单")
        update_sale_customer_apply(db_conn)
        # 4、销售客户订货单物料
        print("4、更新销售客户订货单物料")
        update_sale_customer_apply_item(db_conn)
        # 5、销售申请单
        print("5、更新销售申请单")
        update_sale_order_apply(db_conn)
        # 6、销售申请单物料
        print("6、更新销售申请单物料")
        update_sale_order_apply_item(db_conn)
        # 7、更新销售出库表
        print("7、更新销售出库表")
        update_wms_outbound_form_sale(db_conn)
        # 8、更新发货记录表
        print("8、更新发货记录表")
        update_sale_order_send(db_conn)
        # 9、更新销售退货表
        print("9、更新销售退货表")
        update_act_bpmn_process_log(db_conn)
        # ######################################################
        # 10、更新mrp相关数据（移入mrp文件中）
        print("--更新mrp相关数据--")
        mrp.update_mrp(db_conn)
        # 11、更新prod相关数据（移入prod文件中）
        print("--更新prod相关数据--")
        prod.update_prod(db_conn)
        # 12、更新wms_outbound_form表（与生产领料相关）
        print("12、更新wms_outbound_form表（与生产领料相关）")
        update_wms_outbound_form_produce(db_conn)
        # 13、更新wms_inbound_form表（与产成品入库相关）
        print("13、更新wms_inbound_form表（与产成品入库相关）")
        update_wms_inbound_form(db_conn)
        ########################################################
        # # 新增采购相关更新函数调用
        # 14、更新采购单信息
        print("14、更新采购单信息")
        update_purchase_order_info(db_conn)
        # 15、更新采购单信息
        print("15、更新采购物料信息")
        update_purchase_order_goods_info(db_conn)
        # 16、更新采购入库单信息
        print("16、更新采购入库单信息")
        update_wms_inbound_form_purchase(db_conn)
        # 17、更新采购收货时间
        print("17、更新采购收货时间")
        update_purchase_order_receive_goods_info(db_conn)
        # 18、统一更新采购操作人
        print("18、统一更新采购操作人")
        update_purchase_operators(db_conn)
        # 19、更新采购操作时间
        print("19、更新采购操作时间")
        update_purchase_operation_time(db_conn)

        # 同步销售出库及生产领料时间至拣货管理
        print("20、拣货管理同步")
        syn_pick_management(db_conn)

        # 质量检验更新
        print("--质量检验更新--")
        check.update_check(db_conn)

        # 仓储相关操作
        print("--仓储更新--")
        store.update_store(db_conn)

        # # 基础数据时间更新
        # print("--system更新--")
        # system.update_system(db_conn)

#####################################################
# 单独更新数据
#####################################################
        # # 单独更新产成品入库
        # print("单独更新产成品入库")
        # update_wms_inbound_form_product(db_conn)
        #
        # # 单独更新生产领料
        # print("单独更新生产领料")
        # update_wms_outbound_form_goods(db_conn)
        #
        # # 同步销售出库及生产领料时间至拣货管理
        # print("单独更新产成品入库和生产领料之后进行拣货管理同步")
        # syn_pick_management(db_conn)

        # # 11-1、更新生产子任务和生产工单的实际开始和实际结束时间
        # prod.update_prod_actual_time(db_conn)

######################################################
# 物料、BOM、工艺路线模板导入
######################################################

        # 针对物料、BOM excel数据导入数据库
        # material.process_excel_materials(db_conn,"./goods_BOM_import_349_0828.xlsx")


        # # 针对工艺路线模板 excel数据导入数据库
        # mes_routing_base.process_excel_mes_routing(db_conn,"./mes_routing_import1.xlsx")


        # # 基于物料批量插入工艺路线
        # mes_routing.process_goods_routing(db_conn)


        print("更新数据库完成！")
    finally:
        db_conn.close()
